/*
 * @Author: HeAo
 * @Date: 2021-11-05 10:00:46
 * @LastEditTime: 2021-11-05 12:24:18
 * @LastEditors: HeAo
 * @Description:
 * @FilePath: \web2103-2105\web2103code\4-1-Nodejs\day06\init\app\service\student.js
 * 别乱动！
 */
const { Service } = require('egg')

class StudentService extends Service {
  async findAll () {
    // const sql = `select * from tab_student`
    // let students = await this.app.mysql.query(sql)
    let students = await this.app.mysql.select('tab_student')
    return students
  }

  async saveOrUpdate (student) {
    if (student.id) {
      await this.app.mysql.update('tab_student', student)
    } else {
      await this.app.mysql.insert('tab_student', student)
    }
  }

  async deleteById (id) {
    await this.app.mysql.delete('tab_student', { id })
  }

  async pageQuery ({ page, pageSize, name, gender }) {
    pageSize = parseInt(pageSize)
    // 数据筛选，将空数据从对象中删除掉
    const params = { name, gender }
    for (let k in params) {
      if (!params[k]) {
        delete params[k]
      }
    }
    // 检索学生列表
    let students = await this.app.mysql.select('tab_student', {
      where: params,
      limit: pageSize,
      offset: (page - 1) * pageSize
    })
    // 检索满足条件的学生总数
    let sql = `select count(*) as total from tab_student`
    let keys = Object.keys(params)
    if (keys.length > 0) {
      if (keys.length == 1) {
        sql += ` where ${keys[0]} = '${params[keys[0]]}'`
      } else {
        for (let i = 1; i < Object.keys(params).length; i++) {
          sql += ` where ${keys[0]} = '${params[keys[0]]}' and ${keys[i]} = '${params[keys[i]]}'`
        }

      }
    }
    sql += ` limit ${(page - 1) * pageSize}, ${pageSize}`

    let [{ total }] = await this.app.mysql.query(sql)
    return {
      page,
      pageSize,
      total,
      list: students
    }
  }
}

module.exports = StudentService
